package com.fast.orm.jdbc;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.text.StrBuilder;
import cn.hutool.core.util.BooleanUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import com.fast.orm.config.FastAttributes;
import com.fast.orm.data.DataPackage;
import com.fast.orm.data.Expression;
import com.fast.orm.many.AutoQueryInfo;
import com.fast.orm.many.FastJoinQueryInfo;
import com.fast.orm.mapper.TableMapper;
import com.fast.orm.utils.BeanCopyUtil;

import java.util.*;

/**
 * SQL拼接工具类
 *
 * @author 张亚伟 https://github.com/kaixinzyw
 */
public class MySqlUtil {

    public static final String TABLE_ALIAS_REPLACE = "${tableAlias}";
    public static final String WHERE_PARAM_TYPE = "where_param_";
    public static final String LIMIT_PARAM_TYPE = "limit_param_";
    public static final String UPDATE_PARAM_TYPE = "update_param_";
    public static final String INSERT_PARAM_TYPE = "insert_param_";
    public static final String AS = " AS ";
    public static final String FROM = "FROM ";
    public static final String SET = " SET ";
    public static final String ON = " ON ";
    public static final String AND = DataPackage.Way.AND.expression;
    public static final String OR = DataPackage.Way.OR.expression;
    public static final String WHERE = "WHERE ";
    public static final String WHERE_CRLF = "WHERE " + System.lineSeparator();
    public static final Integer WHERE_CRLF_LENGTH = ("WHERE " + System.lineSeparator()).length();
    public static final String SELECT = "SELECT ";
    public static final String UPDATE = "UPDATE ";
    public static final String DELETE = "DELETE FROM ";
    public static final String INSERT = "INSERT INTO ";
    public static final String COUNT = "COUNT";
    public static final String LIMIT = "LIMIT ";
    public static final String DISTINCT = "DISTINCT";
    public static final String SUM = "SUM";
    public static final String AVG = "AVG";
    public static final String MIN = "MIN";
    public static final String MAX = "MAX";
    public static final String GROUP_BY = "GROUP BY ";
    public static final String NULL = "null";
    public static final String ONE_COUNT = ">0";
    public static final String ORDER_BY = "ORDER BY ";
    public static final String DESC = " DESC ";
    public static final String ASC = " ASC ";
    public static final String CRLF = System.lineSeparator();
    public static final int CRLF_LENGTH = System.lineSeparator().length();
    public static final String COMMA = ", ";
    public static final String EQUAL = " = ";
    public static final String QUOTATION = "`";
    public static final String LEFT_BRACKETS = "(";
    public static final String RIGHT_BRACKETS = ")";
    public static final String VALUES = " VALUES ";
    public static final String WILDCARD = "*";
    public static final String PARAM_PREFIX = "#{";
    public static final String PARAM_PREFIX_2 = "${";
    public static final String MYBATIS_PARAM_PREFIX = "#{paramMap.";
    public static final String MYBATIS_PARAM_PREFIX_2 = "${paramMap.";
    public static final String PARAM_INDEX_PREFIX = "param_";
    public static final String PARAM_SUFFIX = "} ";
    public static final String JDBC_SQL_CONVERSION_RE_RULE = "[#][{](\\w*)[}]";
    public static final String JDBC_SQL_CONVERSION_RE_RULE_2 = "[$][{](\\w*)[}]";
    public static final String JDBC_SQL_CONVERSION_RE_RESULT = ":$1";
    public static final String JDBC_SQL_NEW_TIME_FUNCTION = "NOW()";
    public static String[] SPECIAL_COUNT_QUERY = {
            SUM + StrUtil.SPACE, SUM + LEFT_BRACKETS,
            AVG + StrUtil.SPACE, AVG + LEFT_BRACKETS,
            MIN + StrUtil.SPACE, MIN + LEFT_BRACKETS,
            MAX + StrUtil.SPACE, MAX + LEFT_BRACKETS};

    public static String getUpdateField(DataPackage dataPackage, DataPackage.UpdateField updateField) {
        StrBuilder sql = new StrBuilder();
        String field = getField(dataPackage, updateField.getField());
        sql.append(field).append(EQUAL);
        if (ObjectUtil.equal(updateField.getUpdateType(), DataPackage.UpdateField.UpdateType.CUSTOMIZE)) {
            sql.append(updateField.getValue());
            dataPackage.getParamMap().putAll(updateField.getData());
        } else if (ObjectUtil.equal(updateField.getUpdateType(), DataPackage.UpdateField.UpdateType.EQUAL)) {
            sql.append(packParam(dataPackage, updateField.getValue()));
        } else {
            sql.append(field).append(updateField.getUpdateType().method).append(packParam(dataPackage, updateField.getValue()));
        }
        return sql.toString();
    }

    /**
     * 对更新部分SQL进行封装
     *
     * @param dataPackage dao执行参数
     * @return 封装好更新部分SQL
     */
    public static String updateSql(DataPackage dataPackage) {
        StrBuilder sql = new StrBuilder();
        LinkedHashMap<String, DataPackage.UpdateField> updateFieldMap = dataPackage.getUpdateFieldMap();
        if (BooleanUtil.isTrue(dataPackage.getUpdateOverall())) {
            for (int i = 0; i < dataPackage.getTableMapper().getFieldNames().size(); i++) {
                String fieldName = dataPackage.getTableMapper().getFieldNames().get(i);
                DataPackage.UpdateField updateField = updateFieldMap.get(fieldName);
                if (updateField == null) {
                    sql.append(getField(dataPackage, fieldName)).append(EQUAL).append(NULL);
                } else {
                    sql.append(getUpdateField(dataPackage, updateField));
                }
                if (i < dataPackage.getTableMapper().getFieldNames().size() - 1) {
                    sql.append(StrUtil.COMMA).append(CRLF);
                }
            }
        } else {
            if (updateFieldMap.size() > 0) {
                for (String field : updateFieldMap.keySet()) {
                    DataPackage.UpdateField updateField = updateFieldMap.get(field);
                    sql.append(getUpdateField(dataPackage, updateField)).append(StrUtil.COMMA).append(CRLF);
                }
                sql.del(sql.length() - (1 + CRLF_LENGTH), sql.length());
            }
        }
        if (sql.length() < 1) {
            throw new RuntimeException(dataPackage.getTableMapper().getTableName() + ": 没有需要更新的内容!!!");
        }
        return sql.toString();
    }

    public static String getGroupBy(DataPackage dataPackage) {
        List<DataPackage.GroupByQuery> groupByQueryList = dataPackage.getGroupByQueryList();
        StrBuilder sqlBuilder = new StrBuilder();
        for (int i = 0; i < groupByQueryList.size(); i++) {
            DataPackage.GroupByQuery groupByQuery = groupByQueryList.get(i);
            sqlBuilder.append(getField(dataPackage, groupByQuery.getFieldName()));
            if (i < groupByQueryList.size() - 1) {
                sqlBuilder.append(StrUtil.COMMA);
            }
        }
        return sqlBuilder.toString();
    }


    public static String getOrderBy(DataPackage dataPackage) {
        List<DataPackage.OrderByQuery> orderByList = dataPackage.getOrderByList();
        StrBuilder sqlBuilder = new StrBuilder();
        for (int i = 0; i < orderByList.size(); i++) {
            DataPackage.OrderByQuery orderByQuery = orderByList.get(i);
            sqlBuilder.append(getField(dataPackage, orderByQuery.getOrderByFieldName())).append(StrUtil.SPACE).append(orderByQuery.getOrderByType().name());
            if (i < orderByList.size() - 1) {
                sqlBuilder.append(StrUtil.COMMA);
            }
        }
        return sqlBuilder.toString();
    }


    public static String getField(DataPackage dataPackage, String fieldName) {
        if (dataPackage.isShowTableName()) {
            return StrBuilder.create(dataPackage.getTableAlias(),
                            StrUtil.DOT,
                            dataPackage.getTableMapper().getFiledShowColumnMap().get(fieldName))
                    .toString();
        } else {
            return dataPackage.getTableMapper().getFiledShowColumnMap().get(fieldName);
        }
    }


    public static StrBuilder packParam(DataPackage dataPackage, Object value) {
        long index = dataPackage.getIndex();
        String paramKey = StrBuilder.create(PARAM_INDEX_PREFIX, dataPackage.getTableAlias(), index + "").toString();
        dataPackage.getParamMap().put(paramKey, value);
        return StrBuilder.create(PARAM_PREFIX_2, paramKey, PARAM_SUFFIX);
    }

    public static void setPage(DataPackage dataPackage, Integer pageNum, Integer pageSize, Integer navigatePages) {
        if (pageNum == null || pageNum < 1
                || pageSize == null || pageSize < 1
                || navigatePages == null || navigatePages < 1) {
            throw new RuntimeException("分页参数错误!!!");
        }
        if (pageNum == 1) {
            dataPackage.setPageNumber(0);
        } else {
            dataPackage.setPageNumber((pageNum - 1) * pageSize);
        }
        dataPackage.setPageSize(pageSize);
        dataPackage.setNavigatePages(navigatePages);
    }


    public static void limit(DataPackage dataPackage) {
        StrBuilder limit = dataPackage.getLimit();
        if (dataPackage.getLimitParam() != null) {
            limit.append(LIMIT).append(dataPackage.getLimitParam()).append(CRLF);
        } else if (dataPackage.getPageNumber() != null && dataPackage.getPageSize() != null) {
            limit.append(LIMIT)
                    .append(dataPackage.getPageNumber()).append(COMMA)
                    .append(dataPackage.getPageSize()).append(CRLF);
        }
    }

    public static String getWhere(DataPackage dataPackage, Boolean logicDelete) {
        List<DataPackage.ConditionData> whereList = dataPackage.getWhereList();
        StrBuilder sqlBuilder = new StrBuilder();
        if (logicDelete) {
            sqlBuilder.append(getLogicDelete(dataPackage));
        }
        if (whereList.size() == 0) {
            return sqlBuilder.toString();
        }
        if (sqlBuilder.length() > 0) {
            sqlBuilder.append(CRLF);
        }
        Expression expression = null;
        for (int i = 0; i < whereList.size(); i++) {
            DataPackage.ConditionData conditionData = whereList.get(i);
            StrBuilder condition = whereCondition(conditionData, dataPackage);
            if (StrUtil.isNotBlank(condition)) {
                if (sqlBuilder.length() > 0 && !Expression.LeftBracket.equals(expression)) {
                    sqlBuilder.append(conditionData.getWay().expression);
                }
                sqlBuilder.append(condition);
                if (i < whereList.size() - 1) {
                    sqlBuilder.append(CRLF);
                }
            }
            expression = conditionData.getExpression();

        }
        return sqlBuilder.toString();
    }


    public static StrBuilder whereCondition(DataPackage.ConditionData conditionData, DataPackage dataPackage) {
        StrBuilder sqlBuilder = new StrBuilder();
        Expression expression = conditionData.getExpression();
        switch (conditionData.getExpression()) {
            case In:
            case NotIn:
                sqlBuilder.append(getField(dataPackage, conditionData.getField()))
                        .append(expression.expression)
                        .append(LEFT_BRACKETS)
                        .append(packParam(dataPackage, conditionData.getValue()));
                sqlBuilder.del(sqlBuilder.length() - 1, sqlBuilder.length()).append(RIGHT_BRACKETS);
                break;
            case Match:
            case NotMatch:
                sqlBuilder.append(expression.name).append(LEFT_BRACKETS).append(getField(dataPackage, conditionData.getField()))
                        .append(RIGHT_BRACKETS).append(expression.expression).append(LEFT_BRACKETS)
                        .append(packParam(dataPackage, conditionData.getValue())).append(RIGHT_BRACKETS);
                break;
            case Between:
            case NotBetween:
                sqlBuilder.append(getField(dataPackage, conditionData.getField())).append(expression.expression)
                        .append(packParam(dataPackage, conditionData.getBetweenMin())).append(AND)
                        .append(packParam(dataPackage, conditionData.getBetweenMax()));
                break;
            case Null:
            case NotNull:
                sqlBuilder.append((getField(dataPackage, conditionData.getField()))).append(expression.expression);
                break;
            case LeftBracket:
            case RightBracket:
                sqlBuilder.append(expression.expression);
                break;
            case FindInSet:
            case NotFindInSet:
                sqlBuilder.append(expression.expression).append(LEFT_BRACKETS)
                        .append(packParam(dataPackage, conditionData.getValue()))
                        .append(COMMA).append(getField(dataPackage, conditionData.getField()))
                        .append(RIGHT_BRACKETS);
                break;
            case SQL:
                if (CollUtil.isNotEmpty(conditionData.getParams())) {
                    dataPackage.getParamMap().putAll(conditionData.getParams());
                }
                sqlBuilder.append(conditionData.getSql()).append(CRLF);
                break;
            case Obj:
                Map<String, Object> fieldMap;
                if (conditionData.getValue() instanceof Map) {
                    fieldMap = (Map<String, Object>) conditionData.getValue();
                } else {
                    fieldMap = BeanUtil.beanToMap(conditionData.getValue(), false, true);
                }
                if (CollUtil.isNotEmpty(fieldMap)) {
                    List<String> fieldNames = dataPackage.getTableMapper().getFieldNames();
                    for (String fieldName : fieldMap.keySet()) {
                        if (CollUtil.contains(fieldNames, fieldName)) {
                            Object val = fieldMap.get(fieldName);
                            if (val != null) {
                                sqlBuilder.append(getField(dataPackage, fieldName)).append(expression.expression)
                                        .append(packParam(dataPackage, val))
                                        .append(AND);
                            }
                        }
                    }
                }
                if (StrUtil.lastIndexOf(sqlBuilder, AND, sqlBuilder.length(), false) == sqlBuilder.length() - AND.length()) {
                    sqlBuilder.del(sqlBuilder.length() - AND.length(), sqlBuilder.length());
                }
                break;
            default:
                sqlBuilder.append(getField(dataPackage, conditionData.getField())).append(expression.expression);
                if (conditionData.getBaseMapper()!=null) {
                    sqlBuilder.append(getField(conditionData.getBaseMapper().getDataPackage(), conditionData.getMapperField()));
                }else {
                    sqlBuilder.append(packParam(dataPackage, conditionData.getValue()));
                }
        }
        return sqlBuilder;
    }

    public static String countQueryInfoReplace(String sql) {
        String queryInfo = StrUtil.sub(sql, StrUtil.indexOfIgnoreCase(sql, "SELECT ") + 6, StrUtil.indexOfIgnoreCase(sql, "FROM ")).replace(CRLF, "");
        StrBuilder replaceQueryInfo = StrUtil.strBuilder(COUNT, LEFT_BRACKETS);
        if (StrUtil.containsIgnoreCase(queryInfo, DISTINCT)) {
            replaceQueryInfo.append(queryInfo).append(RIGHT_BRACKETS);
        } else if (StrUtil.containsAnyIgnoreCase(queryInfo, SPECIAL_COUNT_QUERY)) {
            replaceQueryInfo.append(WILDCARD).append(RIGHT_BRACKETS).append(ONE_COUNT);
        } else {
            replaceQueryInfo.append(WILDCARD).append(RIGHT_BRACKETS);
        }
        return StrUtil.strBuilder(SELECT, replaceQueryInfo, CRLF) + StrUtil.sub(sql, StrUtil.indexOfIgnoreCase(sql, "FROM "), sql.length());
    }

    public static String sqlConversion(String sql) {
        if (sql.contains(PARAM_PREFIX)) {
            sql = ReUtil.replaceAll(sql, JDBC_SQL_CONVERSION_RE_RULE, JDBC_SQL_CONVERSION_RE_RESULT);
        }
        if (sql.contains(PARAM_PREFIX_2)) {
            sql = ReUtil.replaceAll(sql, JDBC_SQL_CONVERSION_RE_RULE_2, JDBC_SQL_CONVERSION_RE_RESULT);
        }
        return sql;
    }

    public static String getLogicDelete(DataPackage dataPackage) {
        if (!BooleanUtil.isTrue(dataPackage.getJoin()) && BooleanUtil.isTrue(dataPackage.getLogicDelete())) {
            return StrBuilder.create(getField(dataPackage, FastAttributes.deleteFieldName), EQUAL, FastAttributes.defaultNotDeleteValue, StrUtil.SPACE).toString();
        }
        return StrUtil.EMPTY;
    }

    /**
     * 对新增SQL进行封装
     *
     * @param dataPackage dao执行参数
     * @return 封装好更新部分SQL
     */
    public static String insertSql(DataPackage dataPackage) {
        TableMapper tableMapper = dataPackage.getTableMapper();
        List<String> fieldNames = tableMapper.getFieldNames();
        Map<String, String> fieldTableNames = tableMapper.getFiledShowColumnMap();
        StrBuilder fastSQL = StrUtil.strBuilder(INSERT, tableMapper.getTableName()).append(CRLF);
        List insertList = dataPackage.getInsertList();
        if (insertList.size() == 1) {
            fastSQL.append(SET);
            Object in = insertList.get(0);
            for (String fieldName : fieldNames) {
                Object fieldValue = BeanUtil.getFieldValue(in, fieldName);
                if (fieldValue != null) {
                    fastSQL.append(fieldTableNames.get(fieldName)).append(EQUAL).append(packParam(dataPackage, fieldValue)).append(COMMA);
                }
            }
            fastSQL.del(fastSQL.length() - 2, fastSQL.length());
            fastSQL.append(CRLF);
        } else {
            fastSQL.append(LEFT_BRACKETS);
            fastSQL.append(tableMapper.getQueryRow());
            fastSQL.append(RIGHT_BRACKETS).append(VALUES).append(CRLF);
            for (int x = 0; x < insertList.size(); x++) {
                fastSQL.append(LEFT_BRACKETS);
                for (int i = 0; i < fieldNames.size(); i++) {
                    Object fieldValue = BeanUtil.getFieldValue(insertList.get(x), fieldNames.get(i));
                    fastSQL.append(packParam(dataPackage, fieldValue));
                    if (i < fieldNames.size() - 1) {
                        fastSQL.append(COMMA);
                    }
                }
                fastSQL.append(RIGHT_BRACKETS);
                if (x < insertList.size() - 1) {
                    fastSQL.append(StrUtil.COMMA);
                }
                fastSQL.append(CRLF);
            }
        }
        return fastSQL.toString();
    }

    public static StrBuilder getSelect(DataPackage dataPackage) {
        List<DataPackage.SelectField> selectFieldList = dataPackage.getSelectFieldList();
        StrBuilder select = new StrBuilder();
        if (CollUtil.isEmpty(selectFieldList)) {
            if (!dataPackage.isShowTableName()) {
                select.append(dataPackage.getTableMapper().getQueryRow());
            } else {
                List<String> fieldNameList = dataPackage.getTableMapper().getFieldNames();
                for (int i = 0; i < fieldNameList.size(); i++) {
                    select.append(getField(dataPackage, fieldNameList.get(i)));
                    if (i < fieldNameList.size() - 1) {
                        select.append(StrUtil.COMMA);
                    }
                }
            }
            return select;
        }
        List<String> distinctList = new ArrayList<>();
        for (int i = 0; i < selectFieldList.size(); i++) {
            DataPackage.SelectField selectField = selectFieldList.get(i);
            if (DataPackage.SelectField.SelectType.FIELD.equals(selectField.getType())) {
                select.append(getField(dataPackage, selectField.getField()));
            } else if (DataPackage.SelectField.SelectType.SUM.equals(selectField.getType())
                    || DataPackage.SelectField.SelectType.AVG.equals(selectField.getType())
                    || DataPackage.SelectField.SelectType.MIN.equals(selectField.getType())
                    || DataPackage.SelectField.SelectType.MAX.equals(selectField.getType())) {
                String field = getField(dataPackage, selectField.getField());
                select.append(selectField.getType().name())
                        .append(LEFT_BRACKETS).append(field).append(RIGHT_BRACKETS).append(AS).append(field);
            } else if (DataPackage.SelectField.SelectType.DISTINCT.equals(selectField.getType())) {
                distinctList.add(getField(dataPackage, selectField.getField()));
            }
            if (i < selectFieldList.size() - 1) {
                select.append(StrUtil.COMMA);
            }
        }
        if (distinctList.size() > 0) {
            StrBuilder distinct = StrBuilder.create(DISTINCT, StrUtil.SPACE, CollUtil.join(distinctList, StrUtil.COMMA));
            if (select.length() > 0) {
                distinct.append(StrUtil.COMMA);
                select.del(select.length() - 1, select.length());
            }
            select.insert(0, distinct);
        }
        return select;
    }

    public static String getDeleteSql(DataPackage dataPackage) {
        StrBuilder sql = StrBuilder.create(MySqlUtil.DELETE, dataPackage.getTableMapper().getTableName(), MySqlUtil.CRLF);
        String where = MySqlUtil.getWhere(dataPackage, Boolean.TRUE);
        if (where.length() > 0) {
            sql.append(MySqlUtil.WHERE).append(where);
        }
        return sql.toString();
    }


    public static String getJoin(DataPackage dataPackage) {
        StrBuilder sql = new StrBuilder();
        List<DataPackage.JoinInfo> joinList = dataPackage.getJoinList();
        for (int i = 0; i < joinList.size(); i++) {
            DataPackage.JoinInfo joinInfo = joinList.get(i);
            sql.append(getJoin(dataPackage, joinInfo));
            if (i < joinList.size() - 1) {
                sql.append(CRLF);
            }
        }
        return sql.toString();
    }

    public static String getJoin(DataPackage dataPackage, DataPackage.JoinInfo joinInfo) {
        StrBuilder sql = new StrBuilder();
        sql.append(joinInfo.getDirection().direction)
                .append(joinInfo.getLeftDataPackage().getTableMapper().getTableName())
                .append(AS).append(joinInfo.getLeftDataPackage().getTableAlias());
        if (joinInfo.getRightDataPackage() == null) {
            joinInfo.setRightDataPackage(dataPackage);
        }
        boolean joinDirection = true;
        if (StrUtil.isBlank(joinInfo.getLeftField())) {
            if (joinInfo.getLeftDataPackage().getTableMapper().getFieldNames().contains(joinInfo.getRightDataPackage().getTableMapper().getDefaultJoinFieldName())) {
                joinInfo.setLeftField(joinInfo.getRightDataPackage().getTableMapper().getDefaultJoinFieldName());
            } else {
                joinInfo.setLeftField(joinInfo.getLeftDataPackage().getTableMapper().getPrimaryKeyField());
                joinDirection = false;
            }
        }
        if (StrUtil.isBlank(joinInfo.getRightField())) {
            if (joinDirection) {
                joinInfo.setRightField(joinInfo.getRightDataPackage().getTableMapper().getPrimaryKeyField());
            } else {
                joinInfo.setRightField(joinInfo.getLeftDataPackage().getTableMapper().getDefaultJoinFieldName());
            }
        }

        sql.append(ON).append(getField(joinInfo.getLeftDataPackage(), joinInfo.getLeftField())).append(EQUAL)
                .append(getField(joinInfo.getRightDataPackage(), joinInfo.getRightField()));
        String logicDelete = getLogicDelete(joinInfo.getLeftDataPackage());
        if (StrUtil.isNotBlank(logicDelete)) {
            sql.append(StrUtil.SPACE).append(AND).append(logicDelete);
        }
        sql.append(joinInfo.getAnd());
        return sql.toString();
    }

    private static final Map<String, String> JOIN_SHOW_PREFIX_ALL_MAP = new HashMap<>();

    public static void packageQuery(DataPackage dataPackage, Boolean isCountQuery) {
        if (dataPackage.getFrom() != null) {
            return;
        }
        StrBuilder selectRow = new StrBuilder();
        StrBuilder fromStr = new StrBuilder();
        StrBuilder whereStr = new StrBuilder();
        StrBuilder orderByStr = new StrBuilder();
        StrBuilder groupByStr = new StrBuilder(getGroupBy(dataPackage));
        if (!isCountQuery) {
            selectRow.insert(0, MySqlUtil.getSelect(dataPackage));
        }
        if (dataPackage.isShowTableName()) {
            fromStr.insert(0, StrUtil.strBuilder(dataPackage.getTableMapper().getTableName(), AS, dataPackage.getTableAlias()));
        } else {
            fromStr.insert(0, dataPackage.getTableMapper().getTableName());
        }
        whereStr.insert(0, MySqlUtil.getWhere(dataPackage, Boolean.TRUE));
        if (!isCountQuery) {
            orderByStr.append(MySqlUtil.getOrderBy(dataPackage));
        }
        if (CollUtil.isNotEmpty(dataPackage.getJoinList())) {
            List<DataPackage.JoinInfo> joinList = dataPackage.getJoinList();
            for (int i = 0; i < joinList.size(); i++) {
                DataPackage.JoinInfo joinInfo = joinList.get(i);
                fromStr.append(CRLF).append(getJoin(dataPackage, joinInfo));
                String joinWhere = MySqlUtil.getWhere(joinInfo.getLeftDataPackage(), Boolean.FALSE);
                if (StrUtil.isNotBlank(joinWhere)) {
                    dataPackage.getParamMap().putAll(joinInfo.getLeftDataPackage().getParamMap());
                    whereStr.append(MySqlUtil.CRLF).append(AND).append(joinWhere);
                }
                String groupBy = MySqlUtil.getGroupBy(joinInfo.getLeftDataPackage());
                if (StrUtil.isNotBlank(groupBy)) {
                    groupByStr.append(StrUtil.COMMA).append(groupBy);
                }
                if (!isCountQuery) {
                    String joinRow = getJoinRow(dataPackage, joinInfo);
                    if (StrUtil.isNotBlank(joinRow)) {
                        selectRow.append(CRLF).append(StrUtil.COMMA).append(joinRow);
                    }
                    String joinOrderBy = MySqlUtil.getOrderBy(joinInfo.getLeftDataPackage());
                    if (StrUtil.isNotBlank(joinOrderBy)) {
                        orderByStr.append(StrUtil.COMMA).append(joinOrderBy);
                    }
                }
            }
        }
        dataPackage.setSelect(selectRow);
        dataPackage.setFrom(fromStr);
        dataPackage.setWhere(whereStr);
        dataPackage.setOrderBy(orderByStr);
        dataPackage.setGroupBy(groupByStr);
    }

    public static String getQuery(DataPackage dataPackage) {
        packageQuery(dataPackage, Boolean.FALSE);
        StrBuilder sql = StrBuilder.create(MySqlUtil.SELECT, dataPackage.getSelect(), MySqlUtil.CRLF
                , MySqlUtil.FROM, dataPackage.getFrom());
        if (StrUtil.isNotBlank(dataPackage.getWhere())) {
            sql.append(MySqlUtil.CRLF).append(MySqlUtil.WHERE).append(dataPackage.getWhere());
        }
        if (StrUtil.isNotBlank(dataPackage.getGroupBy())) {
            sql.append(MySqlUtil.CRLF).append(GROUP_BY).append(dataPackage.getGroupBy());
        }
        if (StrUtil.isNotBlank(dataPackage.getOrderBy())) {
            sql.append(MySqlUtil.CRLF).append(MySqlUtil.ORDER_BY).append(dataPackage.getOrderBy());
        }
        if (dataPackage.getLimit().length() > 0) {
            sql.append(MySqlUtil.CRLF).append(dataPackage.getLimit());
        }
        return sql.toString();
    }

    public static String getCount(DataPackage dataPackage) {
        packageQuery(dataPackage, (dataPackage.getPageNumber() == null));
        StrBuilder sql = StrBuilder.create(MySqlUtil.SELECT, MySqlUtil.COUNT,
                MySqlUtil.LEFT_BRACKETS, BooleanUtil.isTrue(dataPackage.isShowTableName()) ? getField(dataPackage, dataPackage.getTableMapper().getPrimaryKeyField()) : dataPackage.getTableMapper().getPrimaryKeyTableField(), MySqlUtil.RIGHT_BRACKETS, MySqlUtil.CRLF,
                MySqlUtil.FROM, dataPackage.getFrom());
        if (StrUtil.isNotBlank(dataPackage.getWhere())) {
            sql.append(MySqlUtil.CRLF).append(MySqlUtil.WHERE).append(dataPackage.getWhere());
        }
        if (StrUtil.isNotBlank(dataPackage.getGroupBy())) {
            sql.append(MySqlUtil.CRLF).append(GROUP_BY).append(dataPackage.getGroupBy());
        }
        return sql.toString();
    }

    private static String getJoinRow(DataPackage dataPackage, DataPackage.JoinInfo joinInfo) {
        DataPackage leftDataPackage = joinInfo.getLeftDataPackage();
        DataPackage rightDataPackage = joinInfo.getRightDataPackage();
        TableMapper returnMapper = dataPackage.getReturnMapper();
        String queryColumnNames = "";
        String tableAlias = leftDataPackage.getTableAlias();
        FastJoinQueryInfo fastJoinQueryInfo = returnMapper.getFastJoinQueryInfoMap().get(tableAlias);
//        if (fastJoinQueryInfo != null && fastJoinQueryInfo.getCollectionType() && dataPackage.getPageNumber() != null) {
//            returnMapper.getFastJoinQueryInfoMap().remove(tableAlias);
//            return queryColumnNames;
//        }
        if (fastJoinQueryInfo != null) {
            String joinKey = StrBuilder.create(returnMapper.getTableAlias(), StrUtil.UNDERLINE,
                    tableAlias).toString();
            queryColumnNames = JOIN_SHOW_PREFIX_ALL_MAP.get(joinKey);
            if (queryColumnNames == null) {
                queryColumnNames = MySqlUtil.getSelect(leftDataPackage).toString();
                JOIN_SHOW_PREFIX_ALL_MAP.put(joinKey, queryColumnNames);
            }
            FastJoinQueryInfo joinQueryInfo = BeanCopyUtil.copy(fastJoinQueryInfo, FastJoinQueryInfo.class);
            if (StrUtil.isEmpty(joinQueryInfo.getThisTableAlias())) {
                joinQueryInfo.setThisTableAlias(rightDataPackage.getTableAlias());
            }
            if (StrUtil.isEmpty(joinQueryInfo.getThisColumnName())) {
                joinQueryInfo.setThisColumnName(rightDataPackage.getTableMapper().getFieldColumnMap().get(joinInfo.getRightField()));
                joinQueryInfo.setThisFieldName(joinInfo.getRightField());
            }
            if (StrUtil.isEmpty(joinQueryInfo.getJoinTableAlias())) {
                joinQueryInfo.setJoinTableAlias(tableAlias);
            }
            if (StrUtil.isEmpty(joinQueryInfo.getJoinColumnName())) {
                joinQueryInfo.setJoinColumnName(leftDataPackage.getTableMapper().getFieldColumnMap().get(joinInfo.getLeftField()));
                joinQueryInfo.setJoinFieldName(joinInfo.getLeftField());
            }
            dataPackage.addFastJoinQueryInfoList(joinQueryInfo);
        }
        if (CollUtil.isNotEmpty(returnMapper.getTableAliasFieldMap().get(tableAlias))) {
            Map<String, String> joinTableAliasFieldMap = returnMapper.getTableAliasFieldMap().get(tableAlias);
            StringBuilder queryRow = new StringBuilder();
            if (queryColumnNames.length() > 0) {
                queryRow.append(StrUtil.COMMA);
            }
            for (String columnName : joinTableAliasFieldMap.keySet()) {
                queryRow.append(tableAlias).append(StrUtil.DOT).append(QUOTATION).append(columnName).append(QUOTATION).append(AS).append(joinTableAliasFieldMap.get(columnName)).append(StrUtil.COMMA);
            }
            if (queryRow.length() > 0) {
                queryRow.delete(queryRow.length() - 1, queryRow.length());
            }
            queryColumnNames = queryColumnNames + queryRow;
        }
        return queryColumnNames;
    }


    public static String getUpdate(DataPackage dataPackage) {
        StrBuilder sql = StrBuilder.create(MySqlUtil.UPDATE, dataPackage.getTableMapper().getTableName(), MySqlUtil.SET, MySqlUtil.CRLF,
                MySqlUtil.updateSql(dataPackage), MySqlUtil.CRLF);
        String where = MySqlUtil.getWhere(dataPackage, Boolean.TRUE);
        if (where.length() > 0) {
            sql.append(MySqlUtil.WHERE).append(where);
        }
        return sql.toString();
    }
}
